// ============================================================================
// FileName: SIPSwitchPersistor.cs
//
// Description:
// Stores and loads SIP records from a Postgresql DB store.
//
// Author(s):
// Aaron Clauson
//
// History:
// 15 Dec 2006	Aaron Clauson	Created.
//
// License: 
// This software is licensed under the BSD License http://www.opensource.org/licenses/bsd-license.php
//
// Copyright (c) 2008 Aaron Clauson (aaronc@blueface.ie), Blue Face Ltd, Dublin, Ireland (www.blueface.ie)
// All rights reserved.
//
// Redistribution and use in source and binary forms, with or without modification, are permitted provided that 
// the following conditions are met:
//
// Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer. 
// Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following 
// disclaimer in the documentation and/or other materials provided with the distribution. Neither the name of Blue Face Ltd. 
// nor the names of its contributors may be used to endorse or promote products derived from this software without specific 
// prior written permission. 
//
// THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, 
// BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. 
// IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, 
// OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, 
// OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, 
// OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE 
// POSSIBILITY OF SUCH DAMAGE.
//-----------------------------------------------------------------------------

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Net;
using System.Runtime.Serialization;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading;
using BlueFace.VoIP.Authentication;
using BlueFace.VoIP.Net;
using BlueFace.VoIP.Net.SIP;
using BlueFace.VoIP.SIPServerCores.StatefulProxy;
using BlueFace.Sys;
using log4net;

namespace BlueFace.VoIP.SIPServer
{
    public class SIPSwitchRegistrarPersistor
    {
        public const int MAX_DIRTYQUEUE_SIZE = 1000;                // Maximum number of items that can be added to the queue waiting to be updated to the database.
        public const int MAX_PERSISTOR_THREADS = 10;                // Maximum number of threads that can be specified to perform database updates (should typically be 3 to 5).
        public const string REG_THREADNAME_KEY = "reg-persist-";
        public const int CHECK_DIRTYRECORDS_INTERVAL = 1000;        // Period the thread will sleep for once all dirty records have been persisted.
        private const int DATABASE_EXPIRY_GRACE_PERIOD = 30;        // Number of seconds after which a contact has exceeded its expiry time that it will be removed from the DB.
        private const int DATABASE_EXPIRY_INTERVAL = 5;             // THe number of seconds between a comb of the database for overdue bindings to remove.

        private static ILog logger = log4net.LogManager.GetLogger("registrar");

        public event ProxyLogDelegate StatefulProxyLogEvent;

        private Queue<SIPRegistrarRecord> m_dirtyRegistrations = new Queue<SIPRegistrarRecord>();    // List of registrations that have been updated and need to be persisted if persistence is being used.
        private StorageLayer m_storageLayer;
        private SIPAccountPersistor m_sipAccountPersistor;
        public Thread[] DBUpdateThreads;

        public bool Stop = false;

        public SIPSwitchRegistrarPersistor(
            StorageTypes storageType, 
            string dbConnStr)
        {
            m_storageLayer = new StorageLayer(storageType, dbConnStr);
            m_sipAccountPersistor = new SIPAccountPersistor(m_storageLayer);
        }

        public void StartPersistorThreads(int numberThreads)
        {
            try
            {
                logger.Debug("SIPSwitchRegistrarPersistor threads starting.");

                if (numberThreads <= 0)
                {
                    numberThreads = 1;
                }
                else if (numberThreads > MAX_PERSISTOR_THREADS)
                {
                    numberThreads = MAX_PERSISTOR_THREADS;
                }

                DBUpdateThreads = new Thread[numberThreads];
                for (int index = 0; index < numberThreads; index++)
                {
                    DBUpdateThreads[index] = new Thread(new ThreadStart(PersistRegistrationToDB));
                    DBUpdateThreads[index].Name = REG_THREADNAME_KEY + index.ToString();
                    DBUpdateThreads[index].Start();
                }
            }
            catch (Exception excp)
            {
                logger.Error("Exception StartPersistorThreads. " + excp.Message);
                throw excp;
            }
        }

        public SIPRegistrarRecord LoadPersistedRegistrarRecord(SIPParameterlessURI addressOfRecord)
        {
            SIPAccount sipAccount = m_sipAccountPersistor.GetSIPAccount(addressOfRecord.User, addressOfRecord.Host);

            if (sipAccount != null)
            {
                SIPRegistrarRecord registrarRecord = new SIPRegistrarRecord(StatefulProxyLogEvent, sipAccount.SIPAccountId, addressOfRecord, addressOfRecord.User, sipAccount.SIPPassword, true);
                return registrarRecord;
            }

            return null;
        }

        public void PersistRegistrationRecord(SIPRegistrarRecord registrarRecord)
        {
            lock (m_dirtyRegistrations)
            {
                if (!m_dirtyRegistrations.Contains(registrarRecord))
                {
                    if (m_dirtyRegistrations.Count < MAX_DIRTYQUEUE_SIZE)
                    {
                        m_dirtyRegistrations.Enqueue(registrarRecord);
                    }
                    else
                    {
                        logger.Error("Dirty registration could not be added as maximum dirty queue size of " + MAX_DIRTYQUEUE_SIZE + " has been reached.");
                    }
                }
            }
        }

        private void PersistRegistrationToDB()
        {
            string username = null;
            DateTime lastDBExpiryCheck = DateTime.Now;

            try
            {
                while (!Stop)
                {
                    string registrarRecordUsername = null;
                    string debugSQL = null;

                    try
                    {
                        SIPRegistrarRecord dirtyRecord = null;

                        lock (m_dirtyRegistrations)
                        {
                            if (m_dirtyRegistrations.Count > 0)
                            {
                                dirtyRecord = m_dirtyRegistrations.Dequeue();
                            }
                        }

                        if (dirtyRecord != null)
                        {
                            registrarRecordUsername = dirtyRecord.AuthUser;

                            foreach (SIPAddressBinding binding in dirtyRecord.GetDirtyBindings())
                            {
                                if (binding.Expires >= 0 && binding.RemovalReason == SIPBindingRemovalReason.Unknown)
                                {
                                    string bindingId = binding.BindingId.ToString();
                                    string userAgent = (binding.UserAgent != null) ? Regex.Replace(binding.UserAgent, "'", "''") : null;
                                    username = dirtyRecord.AddressOfRecord.User;
                                    string requestedContact = binding.ContactURI.ToString().Replace("'", "''");
                                    string fullContact = binding.MangledContactURI.ToString().Replace("'", "''");
                                    DateTime expiresAt = DateTime.Now.AddSeconds(binding.Expires);
                                    string proxySocketStr = (binding.ProxyEndPoint != null) ? "'" + binding.ProxyEndPoint + "'" : "null";

                                    DateTime updateUserStart = DateTime.Now;

                                    // Check if there is already an entry for this contact.
                                    string existingContactId = m_storageLayer.ExecuteScalar(
                                        "select contactid from sipaccountcontacts where " +
                                        "contacturi = '" + fullContact + "' and " +
                                        "sipaccountid =  '" + dirtyRecord.SIPAccountId + "'") as string;

                                    if (existingContactId != null)
                                    {
                                        // This contacturi already exists for the owning SIP account.

                                        if (existingContactId == bindingId)
                                        {
                                            // The binding id matches in the database and registrar so just update the particulars.
                                            string updateSQL =
                                                 "update sipaccountcontacts set " +
                                                 "contacturi = '" + fullContact + "', " +
                                                 "useragent = '" + userAgent + "', " +
                                                 "expiresat = '" + expiresAt.ToString("dd MMM yyyy HH:mm:ss") + "', " +
                                                 "lastupdate = '" + binding.LastUpdateTime.ToString("dd MMM yyyy HH:mm:ss") + "', " +
                                                 "proxysocket = " + proxySocketStr + ", " +
                                                 "proxyprotocol = '" + binding.ProxyProtocol.ToString() + "' " +
                                                 "where " +
                                                 "contactid = '" + bindingId + "'";

                                            debugSQL = updateSQL;

                                            m_storageLayer.ExecuteNonQuery(updateSQL);

                                            FireProxyLogEvent(new ProxyMonitorEvent(ProxyServerTypesEnum.Registrar, ProxyEventTypesEnum.RegistrarPersistence, "Updating binding record for " + fullContact + ".", dirtyRecord.AuthUser));
                                        }
                                        else
                                        {
                                            // The binding id does not match in the database and registrar. This will happen if the Registrar is restarted before the binding expires.
                                            // In this case it's necessary to switch the record to the latest registered binding so as to avoid the contact being expired. 
                                            // Note the contactid being changed in the SQL below.

                                            // If the binding previously pointed to a different contact uri it needs to be deleted.
                                            m_storageLayer.ExecuteNonQuery("delete from sipaccountcontacts where contactid = '" + bindingId + "'");

                                            string updateSQL =
                                                "update sipaccountcontacts set " +
                                                "contacturi = '" + fullContact + "', " +
                                                "requestedcontacturi = '" + requestedContact + "'," + 
                                                "useragent = '" + userAgent + "', " +
                                                "expiresat = '" + expiresAt.ToString("dd MMM yyyy HH:mm:ss") + "', " +
                                                "lastupdate = '" + binding.LastUpdateTime.ToString("dd MMM yyyy HH:mm:ss") + "', " +
                                                "proxysocket = " + proxySocketStr + ", " +
                                                "proxyprotocol = '" + binding.ProxyProtocol.ToString() + "', " +
                                                "contactid = '" + bindingId + "' " +
                                                "where " +
                                                "contactid = '" + existingContactId + "'";

                                            debugSQL = updateSQL;

                                            m_storageLayer.ExecuteNonQuery(updateSQL);

                                            FireProxyLogEvent(new ProxyMonitorEvent(ProxyServerTypesEnum.Registrar, ProxyEventTypesEnum.RegistrarPersistence, "Replacing existing duplicate binding record for " + fullContact + ".", dirtyRecord.AuthUser));
                                        }
                                    }
                                    else
                                    {
                                        // There is no record in the database for this SIP account and the Contact URI.

                                        // Need to check whether the binding is already in the database under a different contact URI.
                                        bool exists = Convert.ToInt32(m_storageLayer.ExecuteScalar("select count(*) from sipaccountcontacts where contactid = '" + bindingId + "'")) > 0;

                                        if (!exists)
                                        {
                                            string insertSQL = "insert into sipaccountcontacts (" +
                                                "contactid," +
                                                "sipaccountid," +
                                                "contacturi," +
                                                "requestedcontacturi," + 
                                                "proxysocket," +
                                                "proxyprotocol," +
                                                "useragent," +
                                                "expiresat) values (" +
                                                "'" + bindingId + "', " +
                                                "'" + dirtyRecord.SIPAccountId + "', " +
                                                "'" + fullContact + "'," +
                                                "'" + requestedContact + "', " + 
                                                proxySocketStr + "," +
                                                "'" + binding.ProxyProtocol.ToString() + "', " +
                                                "'" + userAgent + "'," +
                                                "'" + expiresAt.ToString("dd MMM yyyy HH:mm:ss") + "')";

                                            debugSQL = insertSQL;

                                            m_storageLayer.ExecuteNonQuery(insertSQL);

                                            FireProxyLogEvent(new ProxyMonitorEvent(ProxyServerTypesEnum.Registrar, ProxyEventTypesEnum.RegistrarPersistence, "Inserting binding record for " + fullContact + ".", dirtyRecord.AuthUser));
                                        }
                                        else
                                        {
                                            // This will occur when a binding changes its MANGLED Contact URI.

                                            string updateSQL =
                                            "update sipaccountcontacts set " +
                                            "useragent = '" + userAgent + "', " +
                                            "expiresat = '" + expiresAt.ToString("dd MMM yyyy HH:mm:ss") + "', " +
                                            "lastupdate = '" + binding.LastUpdateTime.ToString("dd MMM yyyy HH:mm:ss") + "', " +
                                            "contacturi = '" + fullContact + "', " +
                                            "proxysocket = " + proxySocketStr + ", " +
                                            "proxyprotocol = '" + binding.ProxyProtocol.ToString() + "' " +
                                            "where " +
                                            "contactid = '" + bindingId + "'";

                                            debugSQL = updateSQL;

                                            m_storageLayer.ExecuteNonQuery(updateSQL);

                                            FireProxyLogEvent(new ProxyMonitorEvent(ProxyServerTypesEnum.Registrar, ProxyEventTypesEnum.RegistrarPersistence, "Updating changed mangled contact URI binding record for " + fullContact + ".", dirtyRecord.AuthUser));
                                        }
                                    }

                                    binding.Dirty = false;
                                    TimeSpan regUpdateTime = DateTime.Now.Subtract(updateUserStart);

                                    string successEventMessage = Thread.CurrentThread.Name + " qsize=" + m_dirtyRegistrations.Count + " optime=" + regUpdateTime.TotalMilliseconds.ToString("0") + "ms " + username + " contact " + fullContact + ".";
                                    FireProxyLogEvent(new ProxyMonitorEvent(ProxyServerTypesEnum.Registrar, ProxyEventTypesEnum.RegistrarPersistence, successEventMessage, null));
                                }
                                else
                                {
                                    if (binding.RemovalReason == SIPBindingRemovalReason.Unknown && binding.Expires <= 0)
                                    {
                                        binding.RemovalReason = SIPBindingRemovalReason.MaxLifetimeReached;
                                    }

                                    // This is an expired binding. Delete from database.
                                    FireProxyLogEvent(new ProxyMonitorEvent(ProxyServerTypesEnum.Registrar, ProxyEventTypesEnum.BindingRemoval, "Binding " + binding.ContactURI.ToString() + " removed due to " + binding.RemovalReason + " (last register " + binding.LastUpdateTime.ToString("HH:mm:ss") + ").", dirtyRecord.AuthUser));
                                    m_storageLayer.ExecuteNonQuery("delete from sipaccountcontacts where contactid = '" + binding.BindingId.ToString() + "'");

                                    lock (dirtyRecord.Bindings)
                                    {
                                        dirtyRecord.Bindings.Remove(binding.ContactURI);
                                    }
                                }
                            }
                        }
                        else
                        {
                            Thread.Sleep(CHECK_DIRTYRECORDS_INTERVAL);
                        }

                        // Check the database for any bindings that have passed their expiry date and delete them. Note this does not remove the bindings from the Registrar however
                        // it will also detect the expiries in due course and it does not matter if the binding is already removed from the database.
                        if (DateTime.Now.Subtract(lastDBExpiryCheck).TotalSeconds > DATABASE_EXPIRY_INTERVAL)
                        {
                            lastDBExpiryCheck = DateTime.Now;

                            DataSet expiredDBContacts = m_storageLayer.GetDataSet(
                                "select * from sipaccountcontacts sc join sipaccounts sp on sc.sipaccountid = sp.sipaccountid " +
                                "where now() > expiresat + interval '" + DATABASE_EXPIRY_GRACE_PERIOD + " seconds' order by sipusername asc");

                            if (expiredDBContacts != null && expiredDBContacts.Tables[0].Rows.Count > 0)
                            {
                                StringBuilder contactIds = new StringBuilder();

                                foreach (DataRow expiredRow in expiredDBContacts.Tables[0].Rows)
                                {
                                    string contactId = expiredRow["contactid"] as string;
                                    string contactURI = expiredRow["contacturi"] as string;
                                    string expiredUsername = expiredRow["sipusername"] as string;
                                    DateTime lastUpated = (DateTime)expiredRow["lastupdate"];

                                    FireProxyLogEvent(new ProxyMonitorEvent(ProxyServerTypesEnum.Registrar, ProxyEventTypesEnum.BindingRemoval, "Expiry time exceeded for " + contactURI + ", removing from database (last update " + lastUpated.ToString("HH:mm:ss") + ")", expiredUsername));

                                    contactIds.Append("'" + contactId + "',");
                                }

                                string contactIdList = "(" + contactIds.ToString().TrimEnd(',') + ")";

                                m_storageLayer.ExecuteNonQuery("delete from sipaccountcontacts where contactid in " + contactIdList);
                            }
                        }
                    }
                    catch (Exception dbExcp)
                    {
                        logger.Error("Exception updating db registration (" + registrarRecordUsername + "). " + dbExcp.Message);
                        logger.Error("SQL: " + debugSQL);
                    }
                }
            }
            catch (Exception excp)
            {
                logger.Error("Exception SIPSwitchRegistrarPersistor. " + excp.Message);
                FireProxyLogEvent(new ProxyMonitorEvent(ProxyServerTypesEnum.Registrar, ProxyEventTypesEnum.Error, "Exception SIPSwitchRegistrarPersistor STOPPED. " + excp.Message, username));
            }
        }

        private void FireProxyLogEvent(ProxyMonitorEvent monitorEvent)
        {
            if (StatefulProxyLogEvent != null)
            {
                try
                {
                    StatefulProxyLogEvent(monitorEvent);
                }
                catch (Exception excp)
                {
                    logger.Error("Exception FireProxyLogEvent SIPSwitchRegistrarPersistor. " + excp.Message);
                }
            }
        }
    }
}
